在以下的文章中，我將以“辦公自動化”系統為例，探討如何在有著1000萬條數據的MS SQL SERVER數據庫中實現快速的數據提取和數據分頁。
以下代碼說明了我們實例中數據庫的“紅頭文件”一表的部分數據結構： [dbo].[TGongwen] (
[Gid] [int] IDENTITY (1, 1) ,
[title] [varchar] (80) COLLATE Chinese_PRC_CI_AS ,
[fariqi] [datetime] ,
[neibuYonghu] [varchar] (70) COLLATE Chinese_PRC_CI_AS ,
[reader] [varchar] (900) COLLATE Chinese_PRC_CI_AS ,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
1000
@i int
@i=1
@i
Tgongwen(fariqi,neibuyonghu,reader,title) (,,,)
@i=@i+1
@i int
@i=1
@i
‘,’,,,,,admin,,,,,,’,’25’)
GO
@h int
@h=1
@h
@i int
@i=2002
@i
@j int
@j=0
@j
@k int
@k=0
@k
Tgongwen(fariqi,neibuyonghu,reader,title) (cast(@i varchar(4))++cast(@j varchar(2))++cast(@j varchar(2)),,,)
@k=@k+1
@j=@j+1
@i=@i+1
@h=@h+1
@i int
@i=1
@i
Tgongwen(fariqi,neibuyonghu,reader,title) (,,,)
@i=@i+1000000
通過以上語句，我們創建了2520042525200496200220031002500502004559001000
index,SELECT
SQL SERVERclustered indexnonclustered index
anazazhang
67263390
2004112004101
1
SQL SERVER
IDID1GidSQL SERVERID
IDIDIDIDID
where1328
ID1000325
1
Select gid
128470128
2fariq
gid,fariqi,neibuyonghu,title Tgongwen
fariqi> dateadd(day,-90,getdate())
用時：5376354
3fariqi
gid,fariqi,neibuyonghu,title Tgongwen
fariqi> dateadd(day,-90,getdate())
用時：24232
251000ID12ID
select @d datetime
@d=getdate()
select
select [(datediff(ms,@d,getdate())
2
23fariqi
1000fariqi5003
3
compound index
25fariqineibuyonghu
1select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>’2004-5-5′
2513
2select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>’2004-5-5′ and neibuyonghu=”
2516
3select gid,fariqi,neibuyonghu,title from Tgongwen where neibuyonghu=”
60280
12
1
25
gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=’2004-9-16′
3326
gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid
4470
1/4
2order by
gid,fariqi,neibuyonghu,reader,title from Tgongwen order by fariqi
12936
gid,fariqi,neibuyonghu,reader,title from Tgongwen order by gid
18843
order by3/1010
3
gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>’2004-1-1′
6343100
gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>’2004-6-6′
317050
gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=’2004-9-16′
3326
gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>’2004-1-1′ and fariqi
3280
4
10020041150505000
gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>’2004-1-1′ order by fariqi
6390
gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi
6453
SQL
SQLSQL SERVERSQLSQL SERVER
* from table1 where name=’zhangsan’ and tID > 10000
:
* from table1 where tID > 10000 and name=’zhangsan’
tID10000name=’zhangsan’tID>10000
SQL SERVERwhere
where
SARG
SARGAND
Name=
>5000
5000
Name= and >5000
SARGSQL SERVERWHERESARG
SARGSARG
1LikeSARG
name like % SARG
name like % ,SARG
%
2or
Name= and >5000 SARGName= or >5000 SARGor
3SARG
SARGNOT!=!NOT EXISTSNOT INNOT LIKESARG
ABS()
Name like %
WHERE *2>5000
SQL SERVERSARGSQL SERVER
WHERE >2500/2
SQL SERVER
4IN OR
Select * from table1 where tid in (2
Select * from table1 where tid=2 or tid=3
tid
5NOT
6exists in
existsinnot existsnot innotSQL SERVERpubsSQL SERVERstatistics I/O
1select title,price from titles where title_id in (select title_id from sales where qty>30)
‘sales’ 18 56 0 0
‘titles’ 1 2 0 0
2select title,price from titles where exists (select * from sales where sales.title_id=titles.title_id and qty>30)
‘sales’ 18 56 0 0
‘titles’ 1 2 0 0
existsin
7charindex()%LIKE
LIKE%charindex()LIKE
select gid,title,fariqi,reader from tgongwen where charindex(”,reader)>0 and fariqi>’2004-5-5′
7 4 7155 0 0
select gid,title,fariqi,reader from tgongwen where reader like ‘%’ + ” + ‘%’ and fariqi>’2004-5-5′
7 4 7155 0 0
8unionor
whereorunionor
gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=’2004-9-16′ or gid>9990000
68 1 404008 283 392163
gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=’2004-9-16′
gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid>9990000
9 8 67489 216 7499
unionor
orunionorunionor
gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=’2004-9-16′ or fariqi=’2004-2-5′
6423 2 14726 1 7176
gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=’2004-9-16′
gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=’2004-2-5′
11640 8 14806 108 1144
9select *
top 10000 gid,fariqi,reader,title from tgongwen order by gid desc
4673
top 10000 gid,fariqi,title from tgongwen order by gid desc
1376
top 10000 gid,fariqi from tgongwen order by gid desc
80
10count(*)count()
*
count(*) from Tgongwen
1500
count(gid) from Tgongwen
1483
count(fariqi) from Tgongwen
3140
count(title) from Tgongwen
52050
count(*)count()count(*)count(*) SQL SERVERcount()
11order by
gidfariqi
top 10000 gid,fariqi,reader,title from tgongwen
196 1 289 1 1527
top 10000 gid,fariqi,reader,title from tgongwen order by gid asc
4720 1 41956 0 1287
top 10000 gid,fariqi,reader,title from tgongwen order by gid desc
4736 1 55350 10 775
top 10000 gid,fariqi,reader,title from tgongwen order by fariqi asc
173 1 290 0 0
top 10000 gid,fariqi,reader,title from tgongwen order by fariqi desc
156 1 289 0 0
order by order by
12TOP
I/0
top 10 * from (
top 10000 gid,fariqi,title from tgongwen
where neibuyonghu=”
by gid desc) as a
by gid asc
1000010I/OI/OTOPTOPSQL SERVERTOPORACLEORACLErownumberTOP
web :ADO ADO
pagination1
(@pagesize int,
@pageindex int
)
nocount on
@indextable (id int identity(1,1),nid int)
@PageLowerBound int
@PageUpperBound int
@PageLowerBound=(@pageindex-1)*@pagesize
@PageUpperBound=@PageLowerBound+@pagesize
rowcount @PageUpperBound
@indextable(nid) gid TGongwen fariqi >dateadd(day,-365,getdate()) fariqi
O.gid,O.mid,O.title,O.fadanwei,O.fariqi TGongwen O,@indextable t O.gid=t.nid
t.id>@PageLowerBound t.id
nocount off
以上存儲過程運用了SQL SERVERCREATE TABLE #TempSQL SERVERADO
nm
publish n m
TOP m-n+1 *
publish
(id
( TOP n-1 id
publish))
id publish
ASP.NET+ C#SQL SERVER
pagination2
(
@SQL nVARCHAR(4000),
@Page int,
@RecsPerPage int,
@ID VARCHAR(255),
@Sort VARCHAR(255)
)
@Str nVARCHAR(4000)
@Str=+CAST(@RecsPerPage VARCHAR(20))++@SQL++@ID+
( TOP () T9 )
PRINT @Str
sp_ExecuteSql @Str
其實，以上語句可以簡化為：
TOP 頁大小 *
Table1
(ID
( TOP 頁大小*頁數 id
表
id))
ID
但這個存儲過程有一個致命的缺點，就是它含有NOT IN
TOP 頁大小 *
Table1
( * ( top (頁大小*頁數) * table1 id) b b.id=a.id )
id
即，用not existsnot in
TOP NOT IN
not existsSQL SERVERTOPTOPTOP
TOPNOT INTOPNOT INNOT IN
max()min()maxmin>
Select top 10 * from table1 where id>200
top 頁大小 *
table1
id>
( (id)
( top ((頁碼-1)*頁大小) id table1 id) T
)
id
在選擇即不重復值，又容易分辨大小的列時，我們通常會選擇主鍵。下表列出了筆者用有著1000GIDGIDgid,fariqi,title11010050010001102550
123
1
10
100 1076
500 540 12943
1000
1 24796
10
25
50
10010001
SQL SERVERSQLWEBSQL
—
pagination3
@tblName varchar(255),
@strGetFields varchar(1000) = ,
@fldName varchar(255)=,
@PageSize int = 10,
@PageIndex int = 1,
@doCount bit = 0,
@OrderType bit = 0,
@strWhere varchar(1500) =
@strSQL varchar(5000)
@strTmp varchar(110)
@strOrder varchar(400)
@doCount != 0
@strWhere !=
@strSQL = + @tblName + +@strWhere
@strSQL = + @tblName +
@OrderType != 0
@strTmp =
@strOrder = + @fldName +
@strTmp =
@strOrder = + @fldName +
@PageIndex = 1
@strWhere !=
@strSQL = + str(@PageSize) ++@strGetFields+ + @tblName + + @strWhere + + @strOrder
@strSQL = + str(@PageSize) ++@strGetFields+ + @tblName + + @strOrder
@strSQL = + str(@PageSize) ++@strGetFields+
+ @tblName + + @fldName + + @strTmp + + @fldName + + str((@PageIndex-1)*@PageSize) + + @fldName + + @tblName + + @strOrder + + @strOrder
@strWhere !=
@strSQL = + str(@PageSize) ++@strGetFields+
+ @tblName + + @fldName + + @strTmp +
+ @fldName + + str((@PageIndex-1)*@PageSize) +
+ @fldName + + @tblName + + @strWhere +
+ @strOrder + + @strWhere + + @strOrder
(@strSQL)
上面的這個存儲過程是一個通用的存儲過程，其注釋已寫在其中了。
9
113
258330
13ACCESS
1
2
12
fariqiID
maxminID
getdate()UNIQUE
maxmin
0
1
2
